<%@ page contentType="text/html; charset=UTF-8" language="java" pageEncoding="UTF-8" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat,java.util.Calendar,java.math.BigDecimal"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>

<title></title>
<%
	String excel = request.getParameter("excel");
	if (excel != null && !excel.equals("")) {
		response.setHeader("Content-disposition","attachment; filename=SearchHealth.xls");
		//response.setCharacterEncoding("UTF-8");
	}
	String number = request.getParameter("name");
	String no = request.getParameter("numbers");
	String date_end = request.getParameter("availableDateEnd");
	String date_begin = request.getParameter("availableDateStart");
	String model = request.getParameter("model");
	
	DataSetTool DST = new DataSetTool();
	
	String searchSql = "select employinnertitle,convert(varchar(10),availableDate,120) as availableDate,bcode_job.employno,bcode.bname,convert(varchar(10),bcode.bird,120) as bird,up_department,bcode.departments,bcode.hr_firstname_en,convert(varchar(10),bcode_job.entrydate,120) as entrydate from bcode inner join bcode_job on bcode.bcode = bcode_job.bcode left join (select * from bcode_cer a where exists (select 1 from bcode_cer where bcode = a.bcode group by bcode having max(availabledate) = a.availabledate)) as bcode_cer on bcode_cer.bcode = bcode.bcode where employstatus = '在职' and jobtype != '临时工'";
	
	if (model.equals("1"))
		searchSql += " and availableDate<= dateadd(day, 30, getdate()) and availableDate>=getdate()";
	
	if (!number.equals("0")) {
		searchSql = searchSql + " and bcode.bcode = '"
				+ number + "'";
	}
	if (!no.equals("0")) {
		searchSql = searchSql + " and cerno like '%"
				+ no + "%'";
	}
	if (!date_end.equals("0")) {	
		searchSql = searchSql + " and availableDate >= '"
				+ date_begin + "'";
	}
	if (!date_begin.equals("0")) {
		searchSql = searchSql + " and availableDate <= '"
				+ date_end + "'";
	}
	searchSql = searchSql + " order by availableDate asc";

	DataSet result = DST.getDataSet(searchSql);
%>
</head>

<body>

	<table style="border-collapse: collapse" border="1" align="center"  cellspacing="0" cellpadding="3" border="1" width="100%" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="border-color:#000000;">
		<tr bgcolor="#e8e8e0" align="center" >
			<td>姓名</td>
			<td>雇佣编号</td>
			<td>部门</td>
			<td>英文名</td>
			<td>出生日期</td>
			<td>职位</td>
			<td>入职日期</td>
		    <td>有效期</td>
		    <td>签收</td>
		</tr>
		<%
			for(int i = 0;i < result.getRowCount();i++){
				
		%>
				<tr>
					<td><%= result.getValue(i,"bname") %></td>
					<td><%=result.getValue(i,"employno") %></td>
					<td><%=result.getValue(i,"departments") %></td>
		
					<td><%=result.getValue(i,"hr_firstname_en") %></td>
					<td><%=result.getValue(i,"bird") %></td>
					<td ><%=result.getValue(i,"employinnertitle") %></td>
					<td ><%=result.getValue(i,"entrydate") %></td>
					
					<td ><%=result.getValue(i,"availableDate") %></td>
			        <td> </td>
					
			</tr>
		<%		
			}
		%>
	</table>
</body>

</html>